package com.eleadmin.order.biz.impl;

import com.eleadmin.common.system.entity.Role;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.BaseDao;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.order.biz.ContainerBillBiz;
import com.eleadmin.order.entity.ContainOceanApply;
import com.eleadmin.order.entity.ContainerBill;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.order.entity.Customer;
import com.eleadmin.order.param.ContainerBillParam;
import com.eleadmin.order.result.ContainerBillWeekNum;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-01-13 11:14:35
 */
@Repository
public class ContainerBillBizImpl extends BaseDao implements ContainerBillBiz {

    @Override
    public JsonResult list(PageResult2<T> page, ContainerBillParam containerBill, User loginUser) throws Exception {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBill> rm = new BeanPropertyRowMapper<ContainerBill>(ContainerBill.class);
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.state");
        sql.append(",c.customer_id,c.customer,c.contacts,c.contacts_phone");
        sql.append(",c.dock,c.dock_arrived_date,c.weight,c.box_type");
        sql.append(",c.vessel,c.voyage,c.price,c.barcode_effective_date");
        sql.append(",c.earliest_arrival_port_date,c.arrival_port,c.bill_of_lading,c.factory_address");
        sql.append(",c.factory_pack_date,c.ef_yard");
        sql.append(",c.ef_dock,c.qr_code,c.car_team_id,c.car_team");
        sql.append(",c.car_no,c.white_card,c.driver_id,c.driver");
        sql.append(",c.driver_phone,c.driver_freight,c.sys_driver_freight,c.box_num");
        sql.append(",c.box_num_pic,c.sealno,c.sealno_pic,c.pre_entry_pic");
        sql.append(",c.annex,c.up_yard,c.back_yard,c.sealed_land");
        sql.append(",c.customer_remark,c.plat_remark,c.drop_box,c.withholding");
        sql.append(",c.cash,c.salesmanId,c.salesman,c.operaterId");
        sql.append(",c.operater,c.create_time,c.create_user,c.update_time");
        sql.append(",c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(",cc.ocean_id,cc.apply_id ");
        sql.append(",cc.ocean_lading,cc.apply_lading ");
        sql.append(" from chf_container_bill c ");
        sql.append(" inner join chf_customer cm on cm.id=c.customer_id ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" left join chf_contain_ocean_apply cc on cc.containbill_id=c.id ");
        sql.append(" where c.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        boolean salesman = false;
        boolean operator = false;
        boolean driver = false;
        for(Role role : loginUser.getRoles()) {
            if(role.getRoleName().equals("业务员")){
                salesman = true;
            }
            if(role.getRoleName().equals("操作")){
                operator = true;
            }
            if(role.getRoleName().equals("司机")){
                driver = true;
            }
            if(role.getRoleName().indexOf("调度")>-1 || role.getRoleName().indexOf("财务")>-1
                    || role.getRoleName().indexOf("管理")>-1){
                operator = false;
                salesman = false;
            }
        }
        if (Utils.isNotEmpty(containerBill)) {
            if (salesman) {
                sql.append(" and c.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (operator) {
                sql.append(" and c.operaterId=:operaterId ");
                map.put("operaterId", loginUser.getUserId()+"");
            }
            if (driver) {
                sql.append(" and c.driver_id=:driver_id ");
                map.put("driver_id", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and (instr(c.customer,:customer)>0 or instr(cm.pinyin,:customer)>0 ) ");
                map.put("customer", containerBill.getCustomer());
            }
            if (Utils.isNotEmpty(containerBill.getBillOfLading())) {
                sql.append(" and instr(c.bill_of_lading,:bill_of_lading)>0 ");
                map.put("bill_of_lading", containerBill.getBillOfLading());
            }
            if (Utils.isNotEmpty(containerBill.getKeywords())) {
                sql.append(" and (instr(c.customer,:keywords)>0 or instr(c.factory_pack_date,:keywords)>0 " +
                        "or instr(c.bill_of_lading,:keywords)>0 or instr(c.box_num,:keywords)>0  or instr(c.sealno,:keywords)>0) ");
                map.put("keywords", containerBill.getKeywords());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryAddress())) {
                sql.append(" and instr(c.factory_address,:factory_address)>0 ");
                map.put("factory_address", containerBill.getFactoryAddress());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch1())) {
                sql.append(" and c.factory_pack_date>=:factoryPackDateSearch1 ");
                map.put("factoryPackDateSearch1", containerBill.getFactoryPackDateSearch1() + " 00:00");
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch2())) {
                sql.append(" and c.factory_pack_date<=:factoryPackDateSearch2 ");
                map.put("factoryPackDateSearch2", containerBill.getFactoryPackDateSearch2() + " 23:59");
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDate())) {
                sql.append(" and c.factory_pack_date REGEXP :factoryPackDate ");
                map.put("factoryPackDate", containerBill.getFactoryPackDate());
            }
            if (Utils.isNotEmpty(containerBill.getVessel())) {
                sql.append(" and instr(c.vessel,:vessel)>0 ");
                map.put("vessel", containerBill.getVessel());
            }
            if (Utils.isNotEmpty(containerBill.getVoyage())) {
                sql.append(" and instr(c.voyage,:voyage)>0 ");
                map.put("voyage", containerBill.getVoyage());
            }
            if (Utils.isNotEmpty(containerBill.getBoxNum())) {
                sql.append(" and instr(c.box_num,:box_num)>0 ");
                map.put("box_num", containerBill.getBoxNum());
            }
            if (Utils.isNotEmpty(containerBill.getDock())) {
                sql.append(" and instr(c.dock,:dock)>0 ");
                map.put("dock", containerBill.getDock());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeam())) {
                sql.append(" and instr(c.car_team,:car_team)>0 ");
                map.put("car_team", containerBill.getCarTeam());
            }
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" and instr(c.car_no,:car_no)>0 ");
                map.put("car_no", containerBill.getCarNo());
            }
            if (Utils.isNotEmpty(containerBill.getDriver())) {
                sql.append(" and instr(c.driver,:driver)>0 ");
                map.put("driver", containerBill.getDriver());
            }
            if (Utils.isNotEmpty(containerBill.getUpYard())) {
                sql.append(" and instr(c.up_yard,:up_yard)>0 ");
                map.put("up_yard", containerBill.getUpYard());
            }
            if (Utils.isNotEmpty(containerBill.getArrivalPort())) {
                sql.append(" and instr(c.arrival_port,:arrival_port)>0 ");
                map.put("arrival_port", containerBill.getArrivalPort());
            }
            if (Utils.isNotEmpty(containerBill.getState())) {
                if (containerBill.getState().equals("进行中")) {
                    sql.append(" and c.state in ('提箱','到厂','离厂','进港') ");
                } else if (containerBill.getState().equals("结算")) {
                    sql.append(" and c.state in ('发起费用清单','应付费用已审核','应收费用已审核','财务应收应付费用已审核','结单') ");
                } else if (containerBill.getState().equals("未结单")) {
                    sql.append(" and c.state != '结单' ");
                } else if (containerBill.getState().equals("对账")) {
                    sql.append(" and (c.state = '应收费用审核' or c.state = '应付费用审核') ");
                } else {
                    sql.append(" and instr(c.state,:state)>0 ");
                    map.put("state", containerBill.getState());
                }
            }
            if (Utils.isNotEmpty(containerBill.getSalesman())) {
                sql.append(" and instr(c.salesman,:salesman)>0 ");
                map.put("salesman", containerBill.getSalesman());
            }
            if (Utils.isNotEmpty(containerBill.getOperater())) {
                sql.append(" and instr(c.operater,:operater)>0 ");
                map.put("operater", containerBill.getOperater());
            }
            if (Utils.isNotEmpty(containerBill.getDropBox())) {
                sql.append(" and instr(c.drop_box,:drop_box)>0 ");
                map.put("drop_box", containerBill.getDropBox());
            }
            if (Utils.isNotEmpty(containerBill.getWithholding())) {
                sql.append(" and instr(c.withholding,:withholding)>0 ");
                map.put("withholding", containerBill.getWithholding());
            }
            if (Utils.isNotEmpty(containerBill.getCash())) {
                sql.append(" and instr(c.cash,:cash)>0 ");
                map.put("cash", containerBill.getCash());
            }
            if (Utils.isNotEmpty(containerBill.getSort()) && Utils.isNotEmpty(containerBill.getOrder())) {
                sql.append(" order by c." + containerBill.getSort() + " " + containerBill.getOrder());
            } else {
                sql.append(" order by c.factory_pack_date asc");
            }
        }
        return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
    }

    @Override
    public List<ContainerBill> list(ContainerBillParam containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBill> rm = new BeanPropertyRowMapper<ContainerBill>(ContainerBill.class);
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.state");
        sql.append(",c.customer_id,c.customer,c.contacts,c.contacts_phone");
        sql.append(",c.dock,c.dock_arrived_date,c.weight,c.box_type");
        sql.append(",c.vessel,c.voyage,c.price,c.barcode_effective_date");
        sql.append(",c.earliest_arrival_port_date,c.arrival_port,c.bill_of_lading,c.factory_address");
        sql.append(",c.factory_pack_date,c.ef_yard");
        sql.append(",c.ef_dock,c.qr_code,c.car_team_id,c.car_team");
        sql.append(",c.car_no,c.white_card,c.driver_id,c.driver");
        sql.append(",c.driver_phone,c.driver_freight,c.sys_driver_freight,c.box_num");
        sql.append(",c.box_num_pic,c.sealno,c.sealno_pic,c.pre_entry_pic");
        sql.append(",c.annex,c.up_yard,c.back_yard,c.sealed_land");
        sql.append(",c.customer_remark,c.plat_remark,c.drop_box,c.withholding");
        sql.append(",c.cash,c.salesmanId,c.salesman,c.operaterId");
        sql.append(",c.operater,c.create_time,c.create_user,c.update_time");
        sql.append(",c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(",cc.ocean_id,cc.apply_id ");
        sql.append(",cc.ocean_lading,cc.apply_lading ");
        sql.append(" from chf_container_bill c ");
        sql.append(" inner join chf_customer cm on cm.id=c.customer_id ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" left join chf_contain_ocean_apply cc on cc.containbill_id=c.id ");
        sql.append(" where c.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        boolean salesman = false;
        boolean operator = false;
        boolean driver = false;
        for(Role role : loginUser.getRoles()) {
            if(role.getRoleName().equals("业务员")){
                salesman = true;
            }
            if(role.getRoleName().equals("操作")){
                operator = true;
            }
            if(role.getRoleName().equals("司机")){
                driver = true;
            }
            if(role.getRoleName().indexOf("调度")>-1 || role.getRoleName().indexOf("财务")>-1
                    || role.getRoleName().indexOf("管理")>-1){
                operator = false;
                salesman = false;
            }
        }
        if (Utils.isNotEmpty(containerBill)) {
            if (salesman) {
                sql.append(" and c.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (operator) {
                sql.append(" and c.operaterId=:operaterId ");
                map.put("operaterId", loginUser.getUserId()+"");
            }
            if (driver) {
                sql.append(" and c.driver_id=:driver_id ");
                map.put("driver_id", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and (instr(c.customer,:customer)>0 or instr(cm.pinyin,:customer)>0 ) ");
                map.put("customer", containerBill.getCustomer());
            }
            if (Utils.isNotEmpty(containerBill.getCustomerEq())) {
                sql.append(" and c.customer=:customerEq ");
                map.put("customerEq", containerBill.getCustomerEq());
            }
            if (Utils.isNotEmpty(containerBill.getBillOfLading())) {
                sql.append(" and instr(c.bill_of_lading,:bill_of_lading)>0 ");
                map.put("bill_of_lading", containerBill.getBillOfLading());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryAddress())) {
                sql.append(" and instr(c.factory_address,:factory_address)>0 ");
                map.put("factory_address", containerBill.getFactoryAddress());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDate())) {
                sql.append(" and DATE_FORMAT(factory_pack_date, '%Y-%m-%d') = :factory_pack_date ");
                map.put("factory_pack_date", containerBill.getFactoryPackDate());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch1())) {
                sql.append(" and c.factory_pack_date>=:factoryPackDateSearch1 ");
                map.put("factoryPackDateSearch1", containerBill.getFactoryPackDateSearch1());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch2())) {
                sql.append(" and c.factory_pack_date<=:factoryPackDateSearch2 ");
                map.put("factoryPackDateSearch2", containerBill.getFactoryPackDateSearch2());
            }
            if (Utils.isNotEmpty(containerBill.getVessel())) {
                sql.append(" and instr(c.vessel,:vessel)>0 ");
                map.put("vessel", containerBill.getVessel());
            }
            if (Utils.isNotEmpty(containerBill.getVoyage())) {
                sql.append(" and instr(c.voyage,:voyage)>0 ");
                map.put("voyage", containerBill.getVoyage());
            }
            if (Utils.isNotEmpty(containerBill.getBoxNum())) {
                sql.append(" and instr(c.box_num,:box_num)>0 ");
                map.put("box_num", containerBill.getBoxNum());
            }
            if (Utils.isNotEmpty(containerBill.getDock())) {
                sql.append(" and instr(c.dock,:dock)>0 ");
                map.put("dock", containerBill.getDock());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeamId())) {
                sql.append(" and instr(c.car_team_id,:car_team_id)>0 ");
                map.put("car_team_id", containerBill.getCarTeamId());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeam())) {
                sql.append(" and instr(c.car_team,:car_team)>0 ");
                map.put("car_team", containerBill.getCarTeam());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeamEq())) {
                sql.append(" and c.car_team=:car_teamEq ");
                map.put("car_teamEq", containerBill.getCarTeamEq());
            }
            if (Utils.isNotEmpty(containerBill.getDriver())) {
                sql.append(" and instr(c.driver,:driver)>0 ");
                map.put("driver", containerBill.getDriver());
            }
            if (Utils.isNotEmpty(containerBill.getDriverId())) {
                sql.append(" and c.driver_id=:driver_id ");
                map.put("driver_id", containerBill.getDriverId());
            }
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" and instr(c.car_no,:car_no)>0 ");
                map.put("car_no", containerBill.getCarNo());
            }
            if (Utils.isNotEmpty(containerBill.getDriver())) {
                sql.append(" and instr(c.driver,:driver)>0 ");
                map.put("driver", containerBill.getDriver());
            }
            if (Utils.isNotEmpty(containerBill.getUpYard())) {
                sql.append(" and instr(c.up_yard,:up_yard)>0 ");
                map.put("up_yard", containerBill.getUpYard());
            }
            if (Utils.isNotEmpty(containerBill.getArrivalPort())) {
                sql.append(" and instr(c.arrival_port,:arrival_port)>0 ");
                map.put("arrival_port", containerBill.getArrivalPort());
            }
            if (Utils.isNotEmpty(containerBill.getState())) {
                if (containerBill.getState().equals("进行中")) {
                    sql.append(" and c.state in ('提箱','到厂','离厂','进港') ");
                } else if (containerBill.getState().equals("结算")) {
                    sql.append(" and c.state in ('发起费用清单','应付费用已审核','应收费用已审核','财务应收应付费用已审核','结单') ");
                } else {
                    sql.append(" and c.state=:state ");
                    map.put("state", containerBill.getState());
                }
            }
            if (Utils.isNotEmpty(containerBill.getSalesman())) {
                sql.append(" and instr(c.salesman,:salesman)>0 ");
                map.put("salesman", containerBill.getSalesman());
            }
            if (Utils.isNotEmpty(containerBill.getOperater())) {
                sql.append(" and instr(c.operater,:operater)>0 ");
                map.put("operater", containerBill.getOperater());
            }
            if (Utils.isNotEmpty(containerBill.getDropBox())) {
                sql.append(" and instr(c.drop_box,:drop_box)>0 ");
                map.put("drop_box", containerBill.getDropBox());
            }
            if (Utils.isNotEmpty(containerBill.getWithholding())) {
                sql.append(" and instr(c.withholding,:withholding)>0 ");
                map.put("withholding", containerBill.getWithholding());
            }
            if (Utils.isNotEmpty(containerBill.getCash())) {
                sql.append(" and instr(c.cash,:cash)>0 ");
                map.put("cash", containerBill.getCash());
            }
            if (Utils.isNotEmpty(containerBill.getSort()) && Utils.isNotEmpty(containerBill.getOrder())) {
                sql.append(" order by c." + containerBill.getSort() + " " + containerBill.getOrder());
            } else {
                sql.append(" order by c.factory_pack_date asc");
            }
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<ContainerBill> listForCustomerFeeList(ContainerBillParam containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBill> rm = new BeanPropertyRowMapper<ContainerBill>(ContainerBill.class);
        StringBuffer sql = new StringBuffer("select distinct c.id,c.organization_id,c.state");
        sql.append(",c.customer_id,c.customer,if(c.customer=:customerEq2,c.customer,'') customer,c.contacts,c.contacts_phone");
        sql.append(",c.dock,c.dock_arrived_date,c.weight,c.box_type");
        sql.append(",c.vessel,c.voyage,c.price,c.barcode_effective_date");
        sql.append(",c.earliest_arrival_port_date,c.arrival_port,c.bill_of_lading,c.factory_address,if(c.customer=:customerEq2,c.factory_address,'') factory_address");
        sql.append(",c.factory_pack_date,c.ef_yard");
        sql.append(",c.ef_dock,c.qr_code,c.car_team_id,c.car_team");
        sql.append(",c.car_no,if(c.customer=:customerEq2,c.car_no,'') car_no,c.white_card,c.driver_id,c.driver");
        sql.append(",c.driver_phone,c.driver_freight,c.sys_driver_freight,c.box_num");
        sql.append(",c.box_num_pic,c.sealno,c.sealno_pic,c.pre_entry_pic");
        sql.append(",c.annex,c.up_yard,c.back_yard,c.sealed_land");
        sql.append(",c.customer_remark,c.plat_remark,c.drop_box,c.withholding");
        sql.append(",c.cash,c.salesmanId,c.salesman,c.operaterId");
        sql.append(",c.operater,c.create_time,c.create_user,c.update_time");
        sql.append(",c.update_user");
        sql.append(",suser.nickname createName ");
        sql.append(",cc.ocean_id,cc.apply_id ");
        sql.append(",cc.ocean_lading,cc.apply_lading ");
        sql.append(" from chf_container_bill c ");
        sql.append(" inner join chf_customer cm on cm.id=c.customer_id ");
        sql.append(" inner join chf_container_bill_fee cbf on cbf.bill_id=c.id ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" left join chf_contain_ocean_apply cc on cc.containbill_id=c.id ");
        sql.append(" where c.deleted=0 ");
        map.put("customerEq2", containerBill.getAutocompletevalue());
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and c.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        boolean salesman = false;
        boolean operator = false;
        boolean driver = false;
        for(Role role : loginUser.getRoles()) {
            if(role.getRoleName().equals("业务员")){
                salesman = true;
            }
            if(role.getRoleName().equals("操作")){
                operator = true;
            }
            if(role.getRoleName().equals("司机")){
                driver = true;
            }
            if(role.getRoleName().indexOf("调度")>-1 || role.getRoleName().indexOf("财务")>-1
                    || role.getRoleName().indexOf("管理")>-1){
                operator = false;
                salesman = false;
            }
        }
        if (Utils.isNotEmpty(containerBill)) {
            if (salesman) {
                sql.append(" and c.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (operator) {
                sql.append(" and c.operaterId=:operaterId ");
                map.put("operaterId", loginUser.getUserId()+"");
            }
            if (driver) {
                sql.append(" and c.driver_id=:driver_id ");
                map.put("driver_id", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and (instr(c.customer,:customer)>0 or instr(cm.pinyin,:customer)>0 ) ");
                map.put("customer", containerBill.getCustomer());
            }
            if (Utils.isNotEmpty(containerBill.getCustomerAbbreviation())) {
                sql.append(" and cbf.invoicename=:invoicename ");
                map.put("invoicename", containerBill.getCustomerAbbreviation());
            }
            if (Utils.isNotEmpty(containerBill.getCustomerEq())) {
                sql.append(" and c.customer=:customerEq ");
                map.put("customerEq", containerBill.getCustomerEq());
            }
            if (Utils.isNotEmpty(containerBill.getBillOfLading())) {
                sql.append(" and instr(c.bill_of_lading,:bill_of_lading)>0 ");
                map.put("bill_of_lading", containerBill.getBillOfLading());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryAddress())) {
                sql.append(" and instr(c.factory_address,:factory_address)>0 ");
                map.put("factory_address", containerBill.getFactoryAddress());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDate())) {
                sql.append(" and DATE_FORMAT(factory_pack_date, '%Y-%m-%d') = :factory_pack_date ");
                map.put("factory_pack_date", containerBill.getFactoryPackDate());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch1())) {
                sql.append(" and c.factory_pack_date>=:factoryPackDateSearch1 ");
                map.put("factoryPackDateSearch1", containerBill.getFactoryPackDateSearch1());
            }
            if (Utils.isNotEmpty(containerBill.getFactoryPackDateSearch2())) {
                sql.append(" and c.factory_pack_date<=:factoryPackDateSearch2 ");
                map.put("factoryPackDateSearch2", containerBill.getFactoryPackDateSearch2());
            }
            if (Utils.isNotEmpty(containerBill.getVessel())) {
                sql.append(" and instr(c.vessel,:vessel)>0 ");
                map.put("vessel", containerBill.getVessel());
            }
            if (Utils.isNotEmpty(containerBill.getVoyage())) {
                sql.append(" and instr(c.voyage,:voyage)>0 ");
                map.put("voyage", containerBill.getVoyage());
            }
            if (Utils.isNotEmpty(containerBill.getBoxNum())) {
                sql.append(" and instr(c.box_num,:box_num)>0 ");
                map.put("box_num", containerBill.getBoxNum());
            }
            if (Utils.isNotEmpty(containerBill.getDock())) {
                sql.append(" and instr(c.dock,:dock)>0 ");
                map.put("dock", containerBill.getDock());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeamId())) {
                sql.append(" and instr(c.car_team_id,:car_team_id)>0 ");
                map.put("car_team_id", containerBill.getCarTeamId());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeam())) {
                sql.append(" and instr(c.car_team,:car_team)>0 ");
                map.put("car_team", containerBill.getCarTeam());
            }
            if (Utils.isNotEmpty(containerBill.getDriver())) {
                sql.append(" and instr(c.driver,:driver)>0 ");
                map.put("driver", containerBill.getDriver());
            }
            if (Utils.isNotEmpty(containerBill.getDriverId())) {
                sql.append(" and c.driver_id=:driver_id ");
                map.put("driver_id", containerBill.getDriverId());
            }
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" and instr(c.car_no,:car_no)>0 ");
                map.put("car_no", containerBill.getCarNo());
            }
            if (Utils.isNotEmpty(containerBill.getDriver())) {
                sql.append(" and instr(c.driver,:driver)>0 ");
                map.put("driver", containerBill.getDriver());
            }
            if (Utils.isNotEmpty(containerBill.getUpYard())) {
                sql.append(" and instr(c.up_yard,:up_yard)>0 ");
                map.put("up_yard", containerBill.getUpYard());
            }
            if (Utils.isNotEmpty(containerBill.getArrivalPort())) {
                sql.append(" and instr(c.arrival_port,:arrival_port)>0 ");
                map.put("arrival_port", containerBill.getArrivalPort());
            }
            if (Utils.isNotEmpty(containerBill.getState())) {
                if (containerBill.getState().equals("进行中")) {
                    sql.append(" and c.state in ('提箱','到厂','离厂','进港') ");
                } else if (containerBill.getState().equals("结算")) {
                    sql.append(" and c.state in ('发起费用清单','应付费用已审核','应收费用已审核','财务应收应付费用已审核','结单') ");
                } else {
                    sql.append(" and c.state=:state ");
                    map.put("state", containerBill.getState());
                }
            }
            if (Utils.isNotEmpty(containerBill.getSalesman())) {
                sql.append(" and instr(c.salesman,:salesman)>0 ");
                map.put("salesman", containerBill.getSalesman());
            }
            if (Utils.isNotEmpty(containerBill.getOperater())) {
                sql.append(" and instr(c.operater,:operater)>0 ");
                map.put("operater", containerBill.getOperater());
            }
            if (Utils.isNotEmpty(containerBill.getDropBox())) {
                sql.append(" and instr(c.drop_box,:drop_box)>0 ");
                map.put("drop_box", containerBill.getDropBox());
            }
            if (Utils.isNotEmpty(containerBill.getWithholding())) {
                sql.append(" and instr(c.withholding,:withholding)>0 ");
                map.put("withholding", containerBill.getWithholding());
            }
            if (Utils.isNotEmpty(containerBill.getCash())) {
                sql.append(" and instr(c.cash,:cash)>0 ");
                map.put("cash", containerBill.getCash());
            }
            if (Utils.isNotEmpty(containerBill.getSort()) && Utils.isNotEmpty(containerBill.getOrder())) {
                sql.append(" order by c." + containerBill.getSort() + " " + containerBill.getOrder());
            } else {
                sql.append(" order by c.factory_pack_date asc");
            }
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<ContainerBill> carPositionList() {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBill> rm = new BeanPropertyRowMapper<ContainerBill>(ContainerBill.class);
        StringBuffer sql = new StringBuffer("select c.id,c.state");
        sql.append(",c.customer_id,c.customer,c.contacts,c.contacts_phone");
        sql.append(",c.bill_of_lading,c.factory_address");
        sql.append(",c.factory_pack_date,c.ef_yard");
        sql.append(",c.car_team_id,c.car_team");
        sql.append(",c.car_no,c.white_card,c.driver_id,c.driver");
        sql.append(",c.driver_phone,c.driver_freight,c.sys_driver_freight,c.box_num");
        sql.append(" from chf_container_bill c ");
        sql.append(" inner join chf_customer cm on cm.id=c.customer_id ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" where c.deleted=0 ");
        sql.append(" and c.state in ('指派车队','提箱','到厂','离厂','进港') ");
        sql.append(" and c.car_no != '' ");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public ContainerBill getById(String id) {
        RowMapper<ContainerBill> rm = new BeanPropertyRowMapper<ContainerBill>(ContainerBill.class);
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select c.id,c.organization_id,c.state");
        sql.append(",c.customer_id,c.customer,c.contacts,c.contacts_phone");
        sql.append(",c.dock,c.dock_arrived_date,c.weight,c.box_type");
        sql.append(",c.vessel,c.voyage,c.price,c.barcode_effective_date");
        sql.append(",c.earliest_arrival_port_date,c.arrival_port,c.bill_of_lading,c.factory_address");
        sql.append(",c.factory_pack_date,c.ef_yard");
        sql.append(",c.ef_dock,c.qr_code,c.car_team_id,c.car_team");
        sql.append(",c.car_no,c.white_card,c.driver_id,c.driver");
        sql.append(",c.driver_phone,c.driver_freight,c.sys_driver_freight,c.box_num");
        sql.append(",c.box_num_pic,c.sealno,c.sealno_pic,c.pre_entry_pic");
        sql.append(",c.annex,c.up_yard,c.back_yard,c.sealed_land");
        sql.append(",c.customer_remark,c.plat_remark,c.drop_box,c.withholding");
        sql.append(",c.cash,c.salesmanId,c.salesman,c.operaterId");
        sql.append(",c.operater,c.create_time,c.create_user,c.update_time");
        sql.append(",c.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_container_bill c ");
        sql.append(" inner join sys_user suser on suser.user_id=c.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=c.organization_id and sogzn.deleted=0 ");
        sql.append(" where c.deleted=0 and c.id=:id ");
        map.put("id", id);
        getMapParameter(sql, map);
        return jdbcTemplate.queryForObject(sql.toString(), map, rm);
    }

    @Override
    public Integer save(ContainerBill containerBill, User loginUser) {
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("insert into chf_container_bill (");
        sql.append("organization_id,state,customer_id,customer");
        sql.append(",contacts,contacts_phone,dock,dock_arrived_date");
        sql.append(",weight,box_type,vessel,voyage");
        sql.append(",price,barcode_effective_date,earliest_arrival_port_date,arrival_port");
        sql.append(",bill_of_lading,factory_address,factory_pack_date");
        sql.append(",ef_yard,ef_dock,qr_code");
        sql.append(",car_team_id,car_team,car_no,white_card");
        sql.append(",driver_id,driver,driver_phone,driver_freight");
        sql.append(",sys_driver_freight,box_num,box_num_pic,sealno");
        sql.append(",sealno_pic,pre_entry_pic,annex,up_yard");
        sql.append(",back_yard,sealed_land,customer_remark,plat_remark");
        sql.append(",drop_box,withholding,cash,salesmanId");
        sql.append(",salesman,operaterId,operater,create_user,create_time) values ( ");
        sql.append(":organization_id,:state,:customer_id,:customer");
        sql.append(",:contacts,:contacts_phone,:dock,:dock_arrived_date");
        sql.append(",:weight,:box_type,:vessel,:voyage");
        sql.append(",:price,:barcode_effective_date,:earliest_arrival_port_date,:arrival_port");
        sql.append(",:bill_of_lading,:factory_address,:factory_pack_date");
        sql.append(",:ef_yard,:ef_dock,:qr_code");
        sql.append(",:car_team_id,:car_team,:car_no,:white_card");
        sql.append(",:driver_id,:driver,:driver_phone,:driver_freight");
        sql.append(",:sys_driver_freight,:box_num,:box_num_pic,:sealno");
        sql.append(",:sealno_pic,:pre_entry_pic,:annex,:up_yard");
        sql.append(",:back_yard,:sealed_land,:customer_remark,:plat_remark");
        sql.append(",:drop_box,:withholding,:cash,:salesmanId");
        sql.append(",:salesman,:operaterId,:operater,:create_user,now())");
        map.put("organization_id", loginUser.getOrganizationId() + "");
        map.put("state", containerBill.getState());
        map.put("customer_id", containerBill.getCustomerId() == null ? null : containerBill.getCustomerId() + "");
        map.put("customer", containerBill.getCustomer());
        map.put("contacts", containerBill.getContacts());
        map.put("contacts_phone", containerBill.getContactsPhone());
        map.put("dock", containerBill.getDock());
        map.put("dock_arrived_date", containerBill.getDockArrivedDate());
        map.put("weight", containerBill.getWeight());
        map.put("box_type", containerBill.getBoxType());
        map.put("vessel", containerBill.getVessel());
        map.put("voyage", containerBill.getVoyage());
        map.put("price", containerBill.getPrice());
        map.put("barcode_effective_date", containerBill.getBarcodeEffectiveDate());
        map.put("earliest_arrival_port_date", containerBill.getEarliestArrivalPortDate());
        map.put("arrival_port", containerBill.getArrivalPort());
        map.put("bill_of_lading", containerBill.getBillOfLading());
        map.put("factory_address", containerBill.getFactoryAddress());
        map.put("factory_pack_date", containerBill.getFactoryPackDate());
        map.put("ef_yard", containerBill.getEfYard());
        map.put("ef_dock", containerBill.getEfDock());
        map.put("qr_code", containerBill.getQrCode());
        map.put("car_team_id", containerBill.getCarTeamId());
        map.put("car_team", containerBill.getCarTeam());
        map.put("car_no", containerBill.getCarNo());
        map.put("white_card", containerBill.getWhiteCard());
        map.put("driver_id", containerBill.getDriverId() == null ? null : containerBill.getDriverId() + "");
        map.put("driver", containerBill.getDriver());
        map.put("driver_phone", containerBill.getDriverPhone());
        map.put("driver_freight", containerBill.getDriverFreight());
        map.put("sys_driver_freight", containerBill.getSysDriverFreight());
        map.put("box_num", containerBill.getBoxNum());
        map.put("box_num_pic", containerBill.getBoxNumPic());
        map.put("sealno", containerBill.getSealno());
        map.put("sealno_pic", containerBill.getSealnoPic());
        map.put("pre_entry_pic", containerBill.getPreEntryPic());
        map.put("annex", containerBill.getAnnex());
        map.put("up_yard", containerBill.getUpYard());
        map.put("back_yard", containerBill.getBackYard());
        map.put("sealed_land", containerBill.getSealedLand());
        map.put("customer_remark", containerBill.getCustomerRemark());
        map.put("plat_remark", containerBill.getPlatRemark());
        map.put("drop_box", containerBill.getDropBox() == null ? null : containerBill.getDropBox() + "");
        map.put("withholding", containerBill.getWithholding() == null ? null : containerBill.getWithholding() + "");
        map.put("cash", containerBill.getCash() == null ? null : containerBill.getCash() + "");
        map.put("salesmanId", containerBill.getSalesmanid() == null ? null : containerBill.getSalesmanid() + "");
        map.put("salesman", containerBill.getSalesman());
        map.put("operaterId", containerBill.getOperaterid() == null ? null : containerBill.getOperaterid() + "");
        map.put("operater", containerBill.getOperater());
        map.put("create_user", loginUser.getUserId() + "");
        getMapParameter(sql, map);
        int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
            @Override
            public boolean hasValue(String paramName) {
                return map.containsKey(paramName);
            }

            @Override
            public Object getValue(String paramName) throws IllegalArgumentException {
                return map.get(paramName);
            }
        }, generatedKeyHolder);

        Integer id = generatedKeyHolder.getKey().intValue();
        return id;
    }

    @Override
    public boolean update(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("state=:state,customer_id=:customer_id,customer=:customer ");
        sql.append(",contacts=:contacts,contacts_phone=:contacts_phone,dock=:dock ");
        sql.append(",dock_arrived_date=:dock_arrived_date,weight=:weight,box_type=:box_type ");
        sql.append(",vessel=:vessel,voyage=:voyage,price=:price ");
        sql.append(",barcode_effective_date=:barcode_effective_date,earliest_arrival_port_date=:earliest_arrival_port_date,arrival_port=:arrival_port ");
        sql.append(",bill_of_lading=:bill_of_lading,factory_address=:factory_address ");
        sql.append(",factory_pack_date=:factory_pack_date,ef_yard=:ef_yard ");
        sql.append(",ef_dock=:ef_dock,qr_code=:qr_code,car_team_id=:car_team_id ");
        sql.append(",car_team=:car_team,car_no=:car_no,white_card=:white_card ");
        sql.append(",driver_id=:driver_id,driver=:driver,driver_phone=:driver_phone ");
        sql.append(",driver_freight=:driver_freight,sys_driver_freight=:sys_driver_freight,box_num=:box_num ");
        sql.append(",box_num_pic=:box_num_pic,sealno=:sealno,sealno_pic=:sealno_pic ");
        sql.append(",pre_entry_pic=:pre_entry_pic,annex=:annex,up_yard=:up_yard ");
        sql.append(",back_yard=:back_yard,sealed_land=:sealed_land,customer_remark=:customer_remark ");
        sql.append(",plat_remark=:plat_remark,drop_box=:drop_box,withholding=:withholding ");
        sql.append(",cash=:cash,salesmanId=:salesmanId,salesman=:salesman ");
        sql.append(",operaterId=:operaterId,operater=:operater,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("state", containerBill.getState());
        map.put("customer_id", containerBill.getCustomerId() == null ? null : containerBill.getCustomerId() + "");
        map.put("customer", containerBill.getCustomer());
        map.put("contacts", containerBill.getContacts());
        map.put("contacts_phone", containerBill.getContactsPhone());
        map.put("dock", containerBill.getDock());
        map.put("dock_arrived_date", containerBill.getDockArrivedDate());
        map.put("weight", containerBill.getWeight());
        map.put("box_type", containerBill.getBoxType());
        map.put("vessel", containerBill.getVessel());
        map.put("voyage", containerBill.getVoyage());
        map.put("price", containerBill.getPrice());
        map.put("barcode_effective_date", containerBill.getBarcodeEffectiveDate());
        map.put("earliest_arrival_port_date", containerBill.getEarliestArrivalPortDate());
        map.put("arrival_port", containerBill.getArrivalPort());
        map.put("bill_of_lading", containerBill.getBillOfLading());
        map.put("factory_address", containerBill.getFactoryAddress());
        map.put("factory_pack_date", containerBill.getFactoryPackDate());
        map.put("ef_yard", containerBill.getEfYard());
        map.put("ef_dock", containerBill.getEfDock());
        map.put("qr_code", containerBill.getQrCode());
        map.put("car_team_id", containerBill.getCarTeamId());
        map.put("car_team", containerBill.getCarTeam());
        map.put("car_no", containerBill.getCarNo());
        map.put("white_card", containerBill.getWhiteCard());
        map.put("driver_id", containerBill.getDriverId() == null ? null : containerBill.getDriverId() + "");
        map.put("driver", containerBill.getDriver());
        map.put("driver_phone", containerBill.getDriverPhone());
        map.put("driver_freight", containerBill.getDriverFreight());
        map.put("sys_driver_freight", containerBill.getSysDriverFreight());
        map.put("box_num", containerBill.getBoxNum());
        map.put("box_num_pic", containerBill.getBoxNumPic());
        map.put("sealno", containerBill.getSealno());
        map.put("sealno_pic", containerBill.getSealnoPic());
        map.put("pre_entry_pic", containerBill.getPreEntryPic());
        map.put("annex", containerBill.getAnnex());
        map.put("up_yard", containerBill.getUpYard());
        map.put("back_yard", containerBill.getBackYard());
        map.put("sealed_land", containerBill.getSealedLand());
        map.put("customer_remark", containerBill.getCustomerRemark());
        map.put("plat_remark", containerBill.getPlatRemark());
        map.put("drop_box", containerBill.getDropBox() == null ? null : containerBill.getDropBox() + "");
        map.put("withholding", containerBill.getWithholding() == null ? null : containerBill.getWithholding() + "");
        map.put("cash", containerBill.getCash() == null ? null : containerBill.getCash() + "");
        map.put("salesmanId", containerBill.getSalesmanid() == null ? null : containerBill.getSalesmanid() + "");
        map.put("salesman", containerBill.getSalesman());
        map.put("operaterId", containerBill.getOperaterid() == null ? null : containerBill.getOperaterid() + "");
        map.put("operater", containerBill.getOperater());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean updateState(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("state=:state,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("state", containerBill.getState());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean updateStateBatch(ContainerBill containerBill, User loginUser) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("state=:state,update_time=now() ");
        sql.append(",update_user=:update_user where id in (:ids) ");
        map.put("state", containerBill.getState());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("ids", containerBill.getIdList());
        getMapParameter(sql, null);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean cash(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("cash=:cash,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("cash", containerBill.getCash()+"");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean dropBox(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("drop_box=:drop_box,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("drop_box", containerBill.getDropBox()+"");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean withholding(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("withholding=:withholding,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("withholding", containerBill.getWithholding()+"");
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean remove(String id, User loginUser) {
        int count = 0;
        Map<String, String> map = null;
        if (id.length() > 0) {
            for (String id1 : id.split(",")) {
                map = new HashMap<String, String>();
                if (id1 == null || id1.equals("")) {
                    break;
                }
                StringBuffer sql = new StringBuffer("update chf_container_bill set deleted=1" +
                        ",update_user=:update_user, update_time=now() "
                        + " where id =:id ");
                map.put("update_user", loginUser.getUserId() + "");
                map.put("id", id1);
                getMapParameter(sql, map);
                count = jdbcTemplate.update(sql.toString(), map);
            }
        }
        return count > 0;
    }


    @Override
    public boolean carTeam(ContainerBill containerBill, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_container_bill set ");
        sql.append("state=:state");
        sql.append(",car_team_id=:car_team_id ");
        sql.append(",car_team=:car_team,car_no=:car_no,white_card=:white_card ");
        sql.append(",driver_id=:driver_id,driver=:driver,driver_phone=:driver_phone ");
        sql.append(",update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("state", containerBill.getState());
        map.put("car_team_id", containerBill.getCarTeamId());
        map.put("car_team", containerBill.getCarTeam());
        map.put("car_no", containerBill.getCarNo());
        map.put("white_card", containerBill.getWhiteCard());
        map.put("driver_id", containerBill.getDriverId() == null ? null : containerBill.getDriverId() + "");
        map.put("driver", containerBill.getDriver());
        map.put("driver_phone", containerBill.getDriverPhone());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", containerBill.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }


    @Override
    public ContainOceanApply getContainerOceanApplyBy(ContainOceanApply containOceanApply) {
        RowMapper<ContainOceanApply> rm = new BeanPropertyRowMapper<ContainOceanApply>(ContainOceanApply.class);
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select ");
        sql.append("c.id");
        sql.append(",c.bh,c.containbill_id,c.ocean_id,c.apply_id");
        sql.append(",c.containbill_lading,c.ocean_lading,c.apply_lading");
        sql.append(" from chf_contain_ocean_apply c ");
        sql.append(" where 1=1 ");
        if (Utils.isNotEmpty(containOceanApply.getContainbillId())) {
            sql.append(" and c.containbill_id=:containbill_id ");
            map.put("containbill_id", containOceanApply.getContainbillId());
        }
        if (Utils.isNotEmpty(containOceanApply.getOceanId())) {
            sql.append(" and c.ocean_id=:ocean_id ");
            map.put("ocean_id", containOceanApply.getOceanId());
        }
        if (Utils.isNotEmpty(containOceanApply.getApplyId())) {
            sql.append(" and c.apply_id=:apply_id ");
            map.put("apply_id", containOceanApply.getApplyId());
        }
        if (Utils.isNotEmpty(containOceanApply.getBh())) {
            sql.append(" and c.bh=:bh ");
            map.put("bh", containOceanApply.getBh());
        }
        getMapParameter(sql, map);
        return jdbcTemplate.queryForObject(sql.toString(), map, rm);
    }

    @Override
    public boolean saveContainerOceanApply(ContainOceanApply containOceanApply) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("insert into chf_contain_ocean_apply (");
        sql.append("bh");
//        if(Utils.isNotEmpty(containOceanApply.getContainbillId())) {
            sql.append(",containbill_id,containbill_lading");
//        }
//        if(Utils.isNotEmpty(containOceanApply.getOceanId())) {
            sql.append(",ocean_id,ocean_lading");
//        }
//        if(Utils.isNotEmpty(containOceanApply.getApplyId())) {
            sql.append(",apply_id,apply_lading");
//        }
        sql.append(") values ( ");
        sql.append(":bh");
//        if(Utils.isNotEmpty(containOceanApply.getContainbillId())) {
            sql.append(",:containbill_id,:containbill_lading");
        map.put("containbill_id", Utils.isNotEmpty(containOceanApply.getContainbillId()) ? containOceanApply.getContainbillId() : null);
            map.put("containbill_lading", containOceanApply.getContainbillLading());
//        }
//        if(Utils.isNotEmpty(containOceanApply.getOceanId())) {
            sql.append(",:ocean_id,:ocean_lading");
        map.put("ocean_id", Utils.isNotEmpty(containOceanApply.getOceanId()) ? containOceanApply.getOceanId() : null);
            map.put("ocean_lading", containOceanApply.getOceanLading());
//        }
//        if(Utils.isNotEmpty(containOceanApply.getApplyId())) {
            sql.append(",:apply_id,:apply_lading");
        map.put("apply_id", Utils.isNotEmpty(containOceanApply.getApplyId()) ? containOceanApply.getApplyId() : null);
            map.put("apply_lading", containOceanApply.getApplyLading());
//        }
        sql.append(")");
        map.put("bh", containOceanApply.getBh());
//        map.put("containbill_id", Utils.isEmpty(containOceanApply.getContainbillId()) ? 0 + "" : containOceanApply.getContainbillId());
//        map.put("ocean_id", Utils.isEmpty(containOceanApply.getOceanId()) ? 0 + "" : containOceanApply.getOceanId());
//        map.put("apply_id", Utils.isEmpty(containOceanApply.getApplyId()) ? 0 + "" : containOceanApply.getApplyId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean updateContainerOceanApply(ContainOceanApply containOceanApply) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_contain_ocean_apply set bh=:bh");
        map.put("bh", containOceanApply.getBh());
//        if(Utils.isNotEmpty(containOceanApply.getContainbillId())) {
        sql.append(",containbill_id=:containbill_id,containbill_lading=:containbill_lading ");
        map.put("containbill_lading", containOceanApply.getContainbillLading());
//        map.put("containbill_id", containOceanApply.getContainbillId());
        map.put("containbill_id", Utils.isNotEmpty(containOceanApply.getContainbillId()) ? containOceanApply.getContainbillId() : null);
//        }

//        if(Utils.isNotEmpty(containOceanApply.getOceanId())) {
        sql.append(",ocean_id=:ocean_id,ocean_lading=:ocean_lading ");
//        map.put("ocean_id", containOceanApply.getOceanId());
        map.put("ocean_id", Utils.isNotEmpty(containOceanApply.getOceanId()) ? containOceanApply.getOceanId() : null);
        map.put("ocean_lading", containOceanApply.getOceanLading());
//        }

//        if(Utils.isNotEmpty(containOceanApply.getApplyId())) {
        sql.append(",apply_id=:apply_id,apply_lading=:apply_lading ");
//        map.put("apply_id", containOceanApply.getApplyId());
        map.put("apply_id", Utils.isNotEmpty(containOceanApply.getApplyId()) ? containOceanApply.getApplyId() : null);
        map.put("apply_lading", containOceanApply.getApplyLading());
//        }
        sql.append(" where id=:id ");
        map.put("id", containOceanApply.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean removeContainerOceanApply(ContainOceanApply containOceanApply) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("delete from chf_contain_ocean_apply  ");
        sql.append(" where id=:id ");
        map.put("id", containOceanApply.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }


    @Override
    public List<ContainerBillWeekNum> listDayNum(ContainerBill containerBill) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBillWeekNum> rm = new BeanPropertyRowMapper<ContainerBillWeekNum>(ContainerBillWeekNum.class);
        StringBuffer sql = new StringBuffer("select ");
        sql.append(" dayT.date ");
        sql.append(" ,count(b.factory_pack_date) num ");
        sql.append(" from (SELECT * FROM (");
        sql.append("SELECT adddate('1970-01-01',t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) date ");
        sql.append("FROM(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,");
        sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,");
        sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,");
        sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,");
        sql.append("(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) tab ");
        sql.append(" WHERE date BETWEEN DATE_FORMAT(:date3,'%Y-%m-%d') AND DATE_FORMAT(:date4,'%Y-%m-%d')) dayT ");
        map.put("date3", containerBill.getDate1().substring(0, 10));
        map.put("date4", containerBill.getDate2().substring(0, 10));
        sql.append(" left join (select b1.factory_pack_date from chf_container_bill b1 ");
        if (Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" inner join chf_customer cm on cm.id=b1.customer_id ");
        }
        sql.append(" where b1.deleted=0  ");
        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and cm.deleted=0 and instr(b1.customer,:customer)>0 ");
                map.put("customer", containerBill.getCustomer());
            }
            if (Utils.isNotEmpty(containerBill.getDate1())) {
                sql.append(" and b1.factory_pack_date>= :date1 ");
                map.put("date1", containerBill.getDate1());
            }
            if (Utils.isNotEmpty(containerBill.getDate2())) {
                sql.append(" and b1.factory_pack_date<=:date2 ");
                map.put("date2", containerBill.getDate2()+" 23:59:59");
            }
        }
        sql.append("  ) b ");
        sql.append(" on DATE_FORMAT(b.factory_pack_date,'%Y-%m-%d')=dayT.date");

        sql.append(" GROUP BY dayT.date ");
        sql.append(" ORDER BY dayT.date asc ");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }


    @Override
    public List<ContainerBillWeekNum> listMonthNum(ContainerBill containerBill) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBillWeekNum> rm = new BeanPropertyRowMapper<ContainerBillWeekNum>(ContainerBillWeekNum.class);
        StringBuffer sql = new StringBuffer("select da.date, ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" en.customer, ");
        }
        sql.append(" en.num from ");
        sql.append(" (SELECT DATE_FORMAT( DATE_SUB( :date1, INTERVAL 1 MONTH ),'%Y-%m') AS date UNION ALL ");
        sql.append(" SELECT DATE_FORMAT(DATE_SUB( :date1, INTERVAL 0 MONTH ),'%Y-%m') AS date ) da ");
        sql.append(" left join  ");
        sql.append(" (select ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" b.customer,");
        }
        sql.append(" DATE_FORMAT(b.factory_pack_date,'%Y-%m') date,count(1) num ");
        sql.append(" from chf_container_bill b ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
        }
        sql.append(" where b.deleted=0 ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" and cm.deleted=0 ");
            if (!containerBill.getCustomer().equals("1")) {
                sql.append(" and instr(b.customer,:customer)>0 ");
                map.put("customer", containerBill.getCustomer());
            }
        }
        sql.append(" and (DATE_FORMAT(DATE_SUB( b.factory_pack_date, INTERVAL -1 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
        sql.append(" or DATE_FORMAT(DATE_SUB( b.factory_pack_date, INTERVAL 0 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
        sql.append(" ) ");
        sql.append(" GROUP BY date ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" ,b.customer ");
        }
        sql.append(" ORDER BY date asc,num desc ");
        sql.append(" ) en ");
        sql.append(" on en.date=da.date ");
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getDate1())) {
            map.put("date1", containerBill.getDate1());
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<ContainerBillWeekNum> listDockNum(ContainerBill containerBill) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBillWeekNum> rm = new BeanPropertyRowMapper<ContainerBillWeekNum>(ContainerBillWeekNum.class);
        StringBuffer sql = new StringBuffer(" ");
        if (containerBill.getDock().equals("week")) {
            sql.append(" select a.* from (SELECT b.factory_address, b.dock customer, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0 and YEARWEEK(DATE_FORMAT(b.factory_pack_date,'%Y-%m-%d')) = YEARWEEK(NOW()) GROUP BY b.factory_address, b.dock     )  a order by a.num desc ");
        } else if (containerBill.getDock().equals("month")) {
            sql.append(" select a.* from (SELECT b.factory_address, b.dock customer, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0 and DATE_FORMAT(b.factory_pack_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY b.factory_address, b.dock    )  a ");
            sql.append(" order by a.num desc");
            sql.append(" limit 5");
        } else if (containerBill.getDock().equals("last month")) {
            sql.append(" select a.* from (SELECT b.factory_address, b.dock customer, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0 and period_diff(date_format(now(),'%Y%m'),date_format(b.factory_pack_date,'%Y%m')) =1 GROUP BY b.factory_address, b.dock    )  a  ");
            sql.append(" order by a.num desc");
            sql.append(" limit 5");
        } else if (containerBill.getDock().equals("quarter")) {
            sql.append(" select a.* from (SELECT b.factory_address, b.dock customer, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0  and QUARTER(b.factory_pack_date)=QUARTER(NOW()) and DATE_FORMAT(b.factory_pack_date,'%Y') = DATE_FORMAT(NOW(),'%Y') GROUP BY b.factory_address, b.dock )  a  ");
            sql.append(" order by a.num desc");
            sql.append(" limit 5");
        } else if (containerBill.getDock().equals("last quarter")) {
            sql.append(" select a.* from (SELECT b.factory_address, b.dock customer, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0  and QUARTER(b.factory_pack_date)=QUARTER(DATE_SUB(now(),INTERVAL 1 QUARTER)) AND year(b.factory_pack_date)=year(DATE_SUB(now(),INTERVAL 1 QUARTER)) GROUP BY b.factory_address, b.dock)  a  ");
            sql.append(" order by a.num desc");
            sql.append(" limit 5");
        } else if (containerBill.getDock().equals("customer")) {
            sql.append(" select bb.factory_address,bb.customer,CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END box_type, CASE ad.price WHEN not null THEN ad.price ELSE mf.price END price  from ");
            sql.append(" (SELECT b.factory_address, b.dock customer, b.box_type, count(1) num FROM chf_container_bill b ");
            sql.append(" WHERE b.deleted=0 and DATE_FORMAT(b.factory_pack_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ");
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and instr(b.customer,:customer)>0 ");
                map.put("customer", containerBill.getCustomer());
            }
            sql.append(" GROUP BY b.factory_address, b.dock,b.box_type  order by num desc  limit 10) bb ");
            sql.append(" left join chf_address ad on ad.address=bb.factory_address and ad.deleted=0  and bb.customer=ad.dock and ad.box_type=CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END ");
            sql.append(" left join chf_maintain_fee mf on mf.address=bb.factory_address and mf.deleted=0 and bb.customer=mf.dock and mf.box_type=CASE LEFT(bb.box_type,2)<21 WHEN 1 THEN '小柜' ELSE '大柜' END");
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<ContainerBillWeekNum> listCustomerAndDriverNum(ContainerBill containerBill) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBillWeekNum> rm = new BeanPropertyRowMapper<ContainerBillWeekNum>(ContainerBillWeekNum.class);
        StringBuffer sql = new StringBuffer("select ");
//		sql.append(" (@i:=@i+1)  i, ");
        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" b.driver carNo,   ");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" b.customer,   ");
            }
        }
        sql.append(" DATE_FORMAT(b.factory_pack_date,'%Y-%m') date,count(1) num ");
        sql.append(" from chf_container_bill b ");
        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" left join chf_customer cm on cm.id=b.customer_id ");
            }
        }
//		sql.append(" ,(select   @i:=0)  t2 ");
        sql.append(" where b.deleted=0 ");
        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" and  b.driver!='' and b.driver is not null ");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and cm.deleted=0 ");
            }
        }
        if (Utils.isNotEmpty(containerBill) && Utils.isNotEmpty(containerBill.getCustomer())) {
            sql.append(" and cm.deleted=0 ");
        }
        sql.append(" and DATE_FORMAT(DATE_SUB( b.factory_pack_date, INTERVAL 0 MONTH ),'%Y-%m')=DATE_FORMAT(:date1,'%Y-%m')   ");
        sql.append(" GROUP BY date ");
        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCarNo())) {
                sql.append(" ,b.driver ");
            }
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" ,b.customer ");
            }
        }
//		sql.append(" having i<6 ");
        sql.append(" ORDER BY date asc,num desc ");
        sql.append(" limit 5");
        map.put("date1", containerBill.getDate1());
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<ContainerBillWeekNum> calendarBillNum(ContainerBill containerBill) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<ContainerBillWeekNum> rm = new BeanPropertyRowMapper<ContainerBillWeekNum>(ContainerBillWeekNum.class);
        StringBuffer sql = new StringBuffer("select  sum(1) num ");
        sql.append(" ,fbi.factory_pack_date ");
        sql.append(" from ( ");
        sql.append(" select bb.id, DATE_FORMAT(bb.factory_pack_date,'%d') factory_pack_date from chf_container_bill bb ");
        sql.append(" where bb.deleted=0 ");

        if (Utils.isNotEmpty(containerBill)) {
            if (Utils.isNotEmpty(containerBill.getCustomer())) {
                sql.append(" and bb.customer=:customer ");
                map.put("customer", containerBill.getCustomer());
            }
            if (Utils.isNotEmpty(containerBill.getDriverPhone())) {
                sql.append(" and bb.driver_phone=:driver_phone ");
                map.put("driver_phone", containerBill.getDriverPhone());
            }
            if (Utils.isNotEmpty(containerBill.getCarTeamId())) {
                sql.append(" and bb.car_team_id=:car_team_id ");
                map.put("car_team_id", containerBill.getCarTeamId());
            }
            if (Utils.isNotEmpty(containerBill.getDate1())) {
                sql.append(" and bb.factory_pack_date>= :date1 ");
                map.put("date1", containerBill.getDate1());
            }
            if (Utils.isNotEmpty(containerBill.getDate2())) {
                sql.append(" and bb.factory_pack_date<=:date2 ");
                map.put("date2", containerBill.getDate2());
            }
        }
        sql.append(" ) fbi ");
        sql.append(" GROUP BY fbi.factory_pack_date ");
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

}
